﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace MobileCommissionManagement.Framework
{
    public class DataAccess : IDisposable
    {
        #region Private Methods

        private static void AddParameters(SqlCommand command, IEnumerable<SqlParameter> parameters)
        {
            Debug.Assert(command != null, "command");
            Debug.Assert(parameters != null, "parameters");

            command.Parameters.Clear();
            foreach (SqlParameter parameter in parameters)
                command.Parameters.Add(parameter);
        }

        private static void PrepareCommand(SqlCommand command, string commandText, IEnumerable<SqlParameter> parameters)
        {
            command.CommandText = commandText;
            AddParameters(command, parameters);
        }

        #endregion

        public void Fill(DataTable table, string connectionString, string query, params SqlParameter[] parameters)
        {
            Debug.Assert(table != null, "table");
            Debug.Assert(query != String.Empty, "query", "'query' argument cannot be empty.");

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand selectCommand = connection.CreateCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = selectCommand;
            PrepareCommand(selectCommand, query, parameters);
            adapter.Fill(table);
            selectCommand.Dispose();
            adapter.Dispose();            
        }

        public Collection<string> ExecuteStringList(string connectionString, string commandText, params SqlParameter[] parameters)
        {
            Collection<string> list = new Collection<string>();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand selectCommand = connection.CreateCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = selectCommand;            
            PrepareCommand(selectCommand, commandText, parameters);
            connection.Open();
            try
            {
                using (SqlDataReader reader = selectCommand.ExecuteReader())
                {
                    while (reader.Read())
                        list.Add(reader.GetString(0));
                }
                return list;
            }
            finally
            {
                connection.Close();
            }
        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

    }
}
